import os
import json
import pandas as pd
import openpyxl
from openpyxl import load_workbook, Workbook
from openpyxl.styles import Font, Alignment, Border, Side
from typing import Dict, List, Any, Optional, Tuple
import logging
from datetime import datetime
import shutil
import zipfile
from pathlib import Path

logger = logging.getLogger(__name__)


class ExcelProcessor:
    """Excel文件处理器"""
    
    def __init__(self, template_config: Dict[str, Any]):
        """
        初始化处理器
        
        Args:
            template_config: 模板配置字典，包含mapping_config, fixed_config等
        """
        self.template_config = template_config
        self.mapping_config = template_config.get('mapping_config', {})
        self.fixed_config = template_config.get('fixed_config', {})
        self.pol_config = template_config.get('pol_config', {})
        self.shipper_config = template_config.get('shipper_config', {})
        
        logger.info(f"Excel处理器初始化完成，配置: {len(self.mapping_config)}个映射规则")
    
    def process_files(self, source_files: List[str], template_file: str, 
                     output_dir: str, task_name: str = None) -> Dict[str, Any]:
        """
        处理文件
        
        Args:
            source_files: 源文件路径列表
            template_file: 模板文件路径
            output_dir: 输出目录
            task_name: 任务名称
            
        Returns:
            处理结果字典
        """
        try:
            logger.info(f"开始处理文件，源文件数量: {len(source_files)}")
            
            # 创建输出目录
            os.makedirs(output_dir, exist_ok=True)
            
            # 读取源文件数据
            source_data = self._read_source_files(source_files)
            
            # 加载模板文件
            template_wb = load_workbook(template_file)
            
            # 处理数据并生成文件
            generated_files = self._generate_files(
                source_data, template_wb, output_dir, task_name
            )
            
            # 创建压缩包
            zip_file = self._create_zip_file(generated_files, output_dir, task_name)
            
            result = {
                'success': True,
                'generated_files': generated_files,
                'zip_file': zip_file,
                'total_files': len(generated_files),
                'message': f'成功生成 {len(generated_files)} 个文件'
            }
            
            logger.info(f"文件处理完成: {result['message']}")
            return result
            
        except Exception as e:
            error_msg = f"文件处理失败: {str(e)}"
            logger.error(error_msg, exc_info=True)
            return {
                'success': False,
                'error': error_msg,
                'generated_files': [],
                'total_files': 0
            }
    
    def _read_source_files(self, source_files: List[str]) -> List[Dict[str, Any]]:
        """
        读取源文件数据
        
        Args:
            source_files: 源文件路径列表
            
        Returns:
            源文件数据列表
        """
        source_data = []
        
        for file_path in source_files:
            try:
                logger.info(f"读取源文件: {file_path}")
                
                # 根据文件扩展名选择读取方式
                file_ext = os.path.splitext(file_path)[1].lower()
                
                if file_ext in ['.xlsx', '.xls']:
                    df = pd.read_excel(file_path)
                elif file_ext == '.csv':
                    df = pd.read_csv(file_path, encoding='utf-8')
                else:
                    logger.warning(f"不支持的文件格式: {file_ext}")
                    continue
                
                # 转换为字典格式
                data = {
                    'file_path': file_path,
                    'file_name': os.path.basename(file_path),
                    'data': df.to_dict('records'),
                    'columns': df.columns.tolist()
                }
                
                source_data.append(data)
                logger.info(f"成功读取文件 {file_path}，数据行数: {len(df)}")
                
            except Exception as e:
                logger.error(f"读取文件 {file_path} 失败: {str(e)}")
                continue
        
        return source_data
    
    def _generate_files(self, source_data: List[Dict[str, Any]], 
                       template_wb: openpyxl.Workbook, output_dir: str,
                       task_name: str = None) -> List[str]:
        """
        生成目标文件
        
        Args:
            source_data: 源数据列表
            template_wb: 模板工作簿
            output_dir: 输出目录
            task_name: 任务名称
            
        Returns:
            生成的文件路径列表
        """
        generated_files = []
        
        # 合并所有源数据
        all_records = []
        for data in source_data:
            all_records.extend(data['data'])
        
        logger.info(f"总数据记录数: {len(all_records)}")
        
        # 根据配置规则分组数据
        grouped_data = self._group_data_by_rules(all_records)
        
        # 为每个分组生成文件
        for group_key, group_records in grouped_data.items():
            try:
                output_file = self._generate_single_file(
                    group_records, template_wb, output_dir, group_key, task_name
                )
                if output_file:
                    generated_files.append(output_file)
                    
            except Exception as e:
                logger.error(f"生成文件 {group_key} 失败: {str(e)}")
                continue
        
        return generated_files
    
    def _group_data_by_rules(self, records: List[Dict[str, Any]]) -> Dict[str, List[Dict[str, Any]]]:
        """
        根据规则分组数据
        
        Args:
            records: 数据记录列表
            
        Returns:
            分组后的数据字典
        """
        grouped_data = {}
        
        for record in records:
            # 默认分组键
            group_key = 'default'
            
            # 根据POL字段分组（如果配置了pol_config）
            if self.pol_config and 'POL' in record:
                pol_value = str(record['POL']).strip()
                if pol_value in self.pol_config:
                    group_key = self.pol_config[pol_value]
                else:
                    group_key = pol_value
            
            # 根据SS Number分组（如果存在）
            elif 'SS No.' in record or 'SS Number' in record:
                ss_number = record.get('SS No.') or record.get('SS Number')
                if ss_number:
                    group_key = f"SS_{str(ss_number).strip()}"
            
            # 根据Number字段分组
            elif 'Number' in record:
                number = record.get('Number')
                if number:
                    group_key = f"NUM_{str(number).strip()}"
            
            # 添加到对应分组
            if group_key not in grouped_data:
                grouped_data[group_key] = []
            grouped_data[group_key].append(record)
        
        logger.info(f"数据分组完成，共 {len(grouped_data)} 个分组")
        return grouped_data
    
    def _generate_single_file(self, records: List[Dict[str, Any]], 
                             template_wb: openpyxl.Workbook, output_dir: str,
                             group_key: str, task_name: str = None) -> Optional[str]:
        """
        生成单个目标文件
        
        Args:
            records: 数据记录列表
            template_wb: 模板工作簿
            output_dir: 输出目录
            group_key: 分组键
            task_name: 任务名称
            
        Returns:
            生成的文件路径
        """
        try:
            # 复制模板工作簿
            new_wb = Workbook()
            new_wb.remove(new_wb.active)  # 删除默认工作表
            
            # 复制模板的所有工作表
            for sheet_name in template_wb.sheetnames:
                template_sheet = template_wb[sheet_name]
                new_sheet = new_wb.create_sheet(sheet_name)
                
                # 复制工作表内容
                self._copy_sheet_content(template_sheet, new_sheet)
            
            # 填充数据
            self._fill_data_to_sheet(new_wb, records)
            
            # 生成文件名
            timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
            filename = f"{group_key}_{timestamp}.xlsx"
            if task_name:
                filename = f"{task_name}_{filename}"
            
            output_file = os.path.join(output_dir, filename)
            
            # 保存文件
            new_wb.save(output_file)
            
            logger.info(f"成功生成文件: {output_file}，数据行数: {len(records)}")
            return output_file
            
        except Exception as e:
            logger.error(f"生成文件 {group_key} 失败: {str(e)}")
            return None
    
    def _copy_sheet_content(self, source_sheet, target_sheet):
        """
        复制工作表内容
        
        Args:
            source_sheet: 源工作表
            target_sheet: 目标工作表
        """
        # 复制单元格值和格式
        for row in source_sheet.iter_rows():
            for cell in row:
                target_cell = target_sheet.cell(
                    row=cell.row, column=cell.column, value=cell.value
                )
                
                # 复制格式
                if cell.has_style:
                    target_cell.font = Font(
                        name=cell.font.name,
                        size=cell.font.size,
                        bold=cell.font.bold,
                        italic=cell.font.italic,
                        color=cell.font.color
                    )
                    target_cell.alignment = Alignment(
                        horizontal=cell.alignment.horizontal,
                        vertical=cell.alignment.vertical
                    )
        
        # 复制列宽
        for col in source_sheet.column_dimensions:
            target_sheet.column_dimensions[col].width = source_sheet.column_dimensions[col].width
        
        # 复制行高
        for row in source_sheet.row_dimensions:
            target_sheet.row_dimensions[row].height = source_sheet.row_dimensions[row].height
    
    def _fill_data_to_sheet(self, workbook: openpyxl.Workbook, records: List[Dict[str, Any]]):
        """
        向工作表填充数据
        
        Args:
            workbook: 工作簿
            records: 数据记录列表
        """
        sheet = workbook.active
        
        # 首先填充固定值配置
        self._fill_fixed_values(sheet)
        
        # 然后根据映射配置填充动态数据
        self._fill_mapped_values(sheet, records)
    
    def _fill_fixed_values(self, sheet):
        """
        填充固定值
        
        Args:
            sheet: 工作表
        """
        for target_key, fixed_value in self.fixed_config.items():
            try:
                # 查找目标关键字在模板中的位置
                target_cell = self._find_target_cell(sheet, target_key)
                if target_cell:
                    target_cell.value = fixed_value
                    logger.debug(f"填充固定值: {target_key} = {fixed_value}")
                    
            except Exception as e:
                logger.warning(f"填充固定值 {target_key} 失败: {str(e)}")
    
    def _fill_mapped_values(self, sheet, records: List[Dict[str, Any]]):
        """
        填充映射值
        
        Args:
            sheet: 工作表
            records: 数据记录列表
        """
        for source_field, mapping_rule in self.mapping_config.items():
            try:
                # 解析映射规则
                target_key, direction, splitter = self._parse_mapping_rule(mapping_rule)
                
                # 收集源数据中的值
                values = []
                for record in records:
                    if source_field in record and record[source_field] is not None:
                        values.append(str(record[source_field]))
                
                if not values:
                    continue
                
                # 查找目标位置
                target_cell = self._find_target_cell(sheet, target_key)
                if not target_cell:
                    continue
                
                # 根据方向和分隔符填充数据
                if direction == 'column':
                    self._fill_column_data(sheet, target_cell, values, splitter)
                else:  # row
                    self._fill_row_data(sheet, target_cell, values, splitter)
                
                logger.debug(f"填充映射值: {source_field} -> {target_key}，数据量: {len(values)}")
                
            except Exception as e:
                logger.warning(f"填充映射值 {source_field} 失败: {str(e)}")
    
    def _parse_mapping_rule(self, mapping_rule: str) -> Tuple[str, str, str]:
        """
        解析映射规则
        
        Args:
            mapping_rule: 映射规则字符串
            
        Returns:
            (目标关键字, 方向, 分隔符)
        """
        # 默认值
        direction = 'column'
        splitter = None
        
        # 分割规则字符串
        parts = mapping_rule.split('|')
        target_key = parts[0].strip()
        
        # 解析参数
        for part in parts[1:]:
            part = part.strip()
            if part.startswith('resdir='):
                direction = part.split('=')[1]
            elif part.startswith('ressplitter='):
                splitter = part.split('=')[1]
        
        return target_key, direction, splitter
    
    def _find_target_cell(self, sheet, target_key: str):
        """
        查找目标关键字在工作表中的位置
        
        Args:
            sheet: 工作表
            target_key: 目标关键字
            
        Returns:
            目标单元格或None
        """
        for row in sheet.iter_rows():
            for cell in row:
                if cell.value and target_key in str(cell.value):
                    return cell
        return None
    
    def _fill_column_data(self, sheet, start_cell, values: List[str], splitter: str = None):
        """
        按列填充数据
        
        Args:
            sheet: 工作表
            start_cell: 起始单元格
            values: 值列表
            splitter: 分隔符
        """
        if splitter:
            # 使用分隔符合并所有值到起始单元格
            combined_value = splitter.join(values)
            start_cell.value = combined_value
        else:
            # 从起始单元格的下一行开始填充
            start_row = start_cell.row + 1
            for i, value in enumerate(values):
                cell = sheet.cell(row=start_row + i, column=start_cell.column)
                cell.value = value
    
    def _fill_row_data(self, sheet, start_cell, values: List[str], splitter: str = None):
        """
        按行填充数据
        
        Args:
            sheet: 工作表
            start_cell: 起始单元格
            values: 值列表
            splitter: 分隔符
        """
        if splitter:
            # 使用分隔符合并所有值到起始单元格
            combined_value = splitter.join(values)
            start_cell.value = combined_value
        else:
            # 从起始单元格的下一列开始填充
            start_col = start_cell.column + 1
            for i, value in enumerate(values):
                cell = sheet.cell(row=start_cell.row, column=start_col + i)
                cell.value = value
    
    def _create_zip_file(self, file_paths: List[str], output_dir: str, 
                        task_name: str = None) -> str:
        """
        创建压缩包
        
        Args:
            file_paths: 文件路径列表
            output_dir: 输出目录
            task_name: 任务名称
            
        Returns:
            压缩包路径
        """
        timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
        zip_name = f"generated_files_{timestamp}.zip"
        if task_name:
            zip_name = f"{task_name}_{zip_name}"
        
        zip_path = os.path.join(output_dir, zip_name)
        
        with zipfile.ZipFile(zip_path, 'w', zipfile.ZIP_DEFLATED) as zipf:
            for file_path in file_paths:
                if os.path.exists(file_path):
                    arcname = os.path.basename(file_path)
                    zipf.write(file_path, arcname)
        
        logger.info(f"创建压缩包: {zip_path}，包含 {len(file_paths)} 个文件")
        return zip_path
    
    @staticmethod
    def validate_template_config(config: Dict[str, Any]) -> Tuple[bool, str]:
        """
        验证模板配置
        
        Args:
            config: 配置字典
            
        Returns:
            (是否有效, 错误信息)
        """
        try:
            # 检查必要的配置项
            required_keys = ['mapping_config']
            for key in required_keys:
                if key not in config:
                    return False, f"缺少必要配置项: {key}"
            
            # 检查映射配置格式
            mapping_config = config['mapping_config']
            if not isinstance(mapping_config, dict):
                return False, "mapping_config必须是字典格式"
            
            # 检查映射规则格式
            for source_field, mapping_rule in mapping_config.items():
                if not isinstance(mapping_rule, str):
                    return False, f"映射规则 {source_field} 必须是字符串格式"
            
            return True, "配置验证通过"
            
        except Exception as e:
            return False, f"配置验证失败: {str(e)}"
    
    @staticmethod
    def get_file_info(file_path: str) -> Dict[str, Any]:
        """
        获取文件信息
        
        Args:
            file_path: 文件路径
            
        Returns:
            文件信息字典
        """
        try:
            file_stat = os.stat(file_path)
            file_ext = os.path.splitext(file_path)[1].lower()
            
            info = {
                'file_path': file_path,
                'file_name': os.path.basename(file_path),
                'file_size': file_stat.st_size,
                'file_ext': file_ext,
                'modified_time': datetime.fromtimestamp(file_stat.st_mtime),
                'is_excel': file_ext in ['.xlsx', '.xls'],
                'is_csv': file_ext == '.csv'
            }
            
            # 如果是Excel文件，获取工作表信息
            if info['is_excel']:
                try:
                    wb = load_workbook(file_path, read_only=True)
                    info['sheet_names'] = wb.sheetnames
                    info['sheet_count'] = len(wb.sheetnames)
                    wb.close()
                except Exception:
                    info['sheet_names'] = []
                    info['sheet_count'] = 0
            
            # 如果是CSV或Excel文件，获取数据信息
            if info['is_excel'] or info['is_csv']:
                try:
                    if info['is_excel']:
                        df = pd.read_excel(file_path, nrows=0)  # 只读取列名
                    else:
                        df = pd.read_csv(file_path, nrows=0, encoding='utf-8')
                    
                    info['columns'] = df.columns.tolist()
                    info['column_count'] = len(df.columns)
                except Exception:
                    info['columns'] = []
                    info['column_count'] = 0
            
            return info
            
        except Exception as e:
            return {
                'file_path': file_path,
                'error': str(e)
            }